# @Project: diablo
# @File: write_agreement_temp
# @Time: 2024/5/13 16:17
# @User: Jolly
# @Email: jolly@007vin.com
"""
车福林 - 临时脚本，指定商户，sales_price价格写入到批发价里
"""

import datetime
from datetime import timedelta
import traceback
import time
import threading
import requests
from queue import Queue
from copy import deepcopy

from io import BytesIO
from openpyxl import load_workbook
from pymongo import MongoClient, InsertOne, UpdateOne
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class InitAgreementPrice:

    def __init__(self):

        self.hongde_main = ["3"]  # 总仓

        self.hongde_yixian = ["1390f84351fe464b90dcdbf3a164ff24"]  # 一线

        self.main_group = ["3", "1390f84351fe464b90dcdbf3a164ff24"]  # 总仓和一线

        # 宏德分公司（除了总仓和一线）
        self.hongde_group = ["fc2e72e26a03423c8f8ee8576debf2ec",  # 龙华鑫众奥
                             "3fd50208a10c42ac87c6d84bd9f5661f",  # 龙华名捷
                             "a62152e4fe1f49d186d23e060f272e65",  # 龙华名诚行
                             "1b5044001e05415ab9f19afbeb73b9c7",  # 罗湖德众
                             "3d80a9253b8640d4afc1da174a39d085",  # 罗湖名捷
                             "230afea849fd489d88aa6dfeab26a3b3",  # 罗湖名诚行
                             "8f5992a1d102410e95cd76f70f0cec30",  # 龙岗德众
                             "c217b4952279478889397f15d9968735",  # 龙岗名捷
                             "84c2ec02335b4f7f8cabcbbda0d133af",  # 龙岗名诚行
                             "411e70375f484ae0b7948b326401a205",  # 平湖鑫德众
                             "b8b351a60d114d3a8a078ad178eb41c5",  # 平湖名捷
                             "f73a74ddc6584457bd573b400e217110",  # 平湖名诚行
                             "0e402537190b4677b5d6d8a1c8af0b2f",  # 寮步鑫众奥
                             "c7823ee69b7b4ed4ae8ddab3ee0c12c7",  # 寮步路捷
                             "92560c123ce347b9806b6756a0d54097",  # 寮步名诚行
                             "79f7412227fb4928bb12f2dc11048a11",  # 沙井鑫众奥
                             "4b2c95dd10654036909423de19777d5d",  # 沙井名捷
                             "920980642c63490abfad211727bbee7d",  # 沙井名诚行
                             "e44f3831dede4640b9bdd40eeaabdc26",  # 宝安鑫众奥
                             "50ff6b30688d40b48879857847dcc320",  # 宝安名捷
                             "192b5c4f311442cbb613ed63a07d8f78",  # 宝安名诚行
                             "c982247f26b74166884d0ca2087d5c17",  # 厚街鑫众奥
                             "acf15daf375f497390ac274538e3e058",  # 厚街名捷
                             "a2f1c6009ca44cbf8b8e6ddb685b5eae",  # 厚街名诚行
                             "3546da2f191b4d1cb8979dad8e42593f",  # 塘厦鑫众奥
                             "203ffd0f7d3645f4ba59387b6b532308",  # 塘厦名捷
                             "cd5ce52f2db34e39bc66e88bc41238b0"   # 塘厦名诚行
                             ]

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        try:
            # 执行任务
            self.task()
        except Exception as e:
            print(traceback.format_exc())

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=300,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 200  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    # 判断定价协议表是否已有对应数据，有的话就删除
    def check_agreement_exist(self, code, agreement_excel_id, merchant_id):

        check = False

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]

                filter_dict = {
                    "code": code,
                    "merchant_id": merchant_id,
                    "agreement_excel_id": agreement_excel_id
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:
                    # 删除已有的
                    # conn.delete_many(filter_dict)
                    check = True
                    return True, filter_dict  # 准备做更新操作

        except Exception as e:
            print("获取协议定价存在检测失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check, {}

    def bulk_operate_agreement_excel(self, bulk_insert_list, bulk_update_list):
        """批量操作 写入、更新等"""

        operate_list = []

        if bulk_insert_list:
            for data in bulk_insert_list:
                operate_list.append(InsertOne(data))

        if bulk_update_list:
            for data in bulk_update_list:
                operate_list.append(UpdateOne(data.get("filter_dict"), data.get("update_dict"), upsert=True))

        # 执行批量更新
        # 获取2.0MongoDB连接
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]
                conn.bulk_write(operate_list)

        except Exception as e:
            print("写入2.0MongoDB数据库单据失败：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 写入数据
    def write_data(self, *args):

        goods = args[0]
        stock_related = args[1]
        sale_price_dict = args[2]  # ["sale_price2": 10, "sale_price3": 100]
        agreement_excel_id = args[3]  # ["sale_price2": 345, "sale_price3": 444]

        code = goods.get("code")

        bulk_insert_list = []
        bulk_update_list = []

        for alias_name, sale_price in sale_price_dict.items():
            # 获取对应模板id
            # agreement_excel_id = agreement_info_dict.get(alias_name)
            # if not agreement_excel_id:
            #     return
            # agreement_excel_id = 497

            filter_dict = {
                "code": code,
                "merchant_id": stock_related.get("merchant_id"),
                "agreement_excel_id": agreement_excel_id
            }

            # 更新操作
            update_dict = {"$set": {
                "agreement_excel_id": agreement_excel_id,
                "code": code,
                "merchant_id": stock_related.get("merchant_id"),
                "agreement_price": sale_price,
                "price_type": "customize",
                "price_value": sale_price,
                "price_desc": f"¥ = {str(sale_price)}",
                "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                "user_id": "0000000000000000003",  # 当前用户id
                "price_user": "系统人工控价",  # 当前用户名
                "price_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            }}

            # 拼入批量列表中
            update_items = {"filter_dict": filter_dict, "update_dict": update_dict}
            bulk_update_list.append(update_items)

        if len(bulk_update_list) > 0:
            try:
                self.bulk_operate_agreement_excel(bulk_insert_list, bulk_update_list)
            except Exception as e:
                print("批量写入定价记录失败：", e)

    def get_stock_related(self, hongde_merchant_ids):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            hongde_group_str = ''
            for index, data in enumerate(hongde_merchant_ids):

                if index == len(hongde_merchant_ids) - 1:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + ""
                    break
                else:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + "'"

            hongde_group_str = hongde_group_str[3:]

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and merchant_id not in ('{hongde_group_str}') """

            print(sql)

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_stock_related2(self, merchant_id):
        """
        查找库存关联表查找有效的库存关系，指定商户
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and merchant_id='{merchant_id}' """

            print(sql)

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_cst_goods(self, cst_merchant_id):
        """
        根据厂商通id查找所有商品数据
        """

        # 测试环境
        # url = "http://192.168.191.131:8900/v1/api/openapi/goods/xqp/alllist"
        # 正式环境
        url = "http://192.168.191.245:9960/v1/api/openapi/goods/xqp/alllist"

        headers = {
            "Content-Type": "application/json"
        }

        params = {
            'yc_id': 'xqp0000000000000000001',
            'merchant_id': cst_merchant_id
        }
        response = requests.get(url=url, headers=headers, params=params)
        resp = response.json()

        # print(resp)

        if resp.get('code') != 1:
            print("厂商通id查找所有商品数据失败")

        return resp['data']

    def check_multiple_price_exist(self, good_info, stock_related):
        """
        查询是否存在多个价格
        """
        url = 'http://192.168.191.245:11180/v2/openapi/goods/erpparts/list'

        # 获取库存配置
        config_dict = {
            'erp_type': stock_related.get('erp_type'),
            'erp_uid': stock_related.get('erp_uid'),
            'app_key': stock_related.get('app_key'),
        }

        # curl -H "Content-Type:application/json" -X POST -d '{"erp_type":"YongXinNet", "erp_uid":"F6D1CF5AB975B20D7F30F55BE66272B6", "app_key": "6f435ff6409197faba1ab2a2f67fddca", "keyword": "1494185-00-B"}' http://192.168.191.245:11180/v2/openapi/goods/erpparts/list

        kwargs = {
            'page': '1',
            'size': '10',
            'keyword': good_info.get("code")
            # 'quality': good_info.get("brand_names")
        }

        kwargs.update(config_dict)

        try:
            res = requests.post(url=url, json=kwargs).json()
            if res.get('code') != 1:
                return True, res
            return False, res

        except Exception as e:
            print(traceback.format_exc())

    def create_agreement_excel_info(self, stock_related, sale_price_dict):
        """
        按照多个价格，新建价格模板信息，如果存在则跳过
        """

        merchant_id = stock_related.get('merchant_id')

        for key, value_ in sale_price_dict.items():

            agreement_info_dict = {}

            mysql_conn = self.mysql_conn.connection()

            try:
                cursor = mysql_conn.cursor()

                sql = f"select merchant_id, id from diablo_excel_agreement where merchant_id='{merchant_id}' and status=1" \
                      f" and alias_name='{key}'"

                cursor.execute(sql)

                result = cursor.fetchall()

                # print(result)

                if len(result) == 0:
                    # 不存在价格模板则新建
                    mysql_conn2 = self.mysql_conn.connection()
                    cursor2 = mysql_conn2.cursor()

                    key_list = ['agreement_name', 'merchant_id', 'status', 'force_time', 'sort',
                                'online_status', 'default_status', 'alias_name', 'fixed_status']

                    force_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

                    if key == "sale_price2":
                        key_param = ["同行价", merchant_id, 1, force_time, 1, 1, 0, key, 0]
                    else:
                        key_param = [key, merchant_id, 1, force_time, 1, 1, 0, key, 0]

                    value = ""

                    for i, v in enumerate(key_list):
                        value += '"{}",'.format(key_param[i])

                    values = ", ".join('{0}=values({0})'.format(k) for k in key_list)

                    insert_sql = '''insert into diablo_excel_agreement ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                        ",".join(key_list), value[:-1], values)
                    # print(sql)
                    cursor2.execute(insert_sql)
                    mysql_conn2.commit()

            except Exception as e:
                print("新建价格模板信息失败 - mysql：", e)
            finally:
                mysql_conn.close()

        return

    def get_agreement_excel_dict(self, merchant_id):
        """
        获取商户价格模板
        """
        agreement_info_dict = {}

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"select alias_name, id from diablo_excel_agreement where merchant_id='{merchant_id}' and status=1" \
                  f" and alias_name in ('sale_price2', 'sale_price3', 'sale_price4', 'sale_price5', 'sale_price6')"

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:

                    agreement_info_dict[result_[0]] = result_[1]

        except Exception as e:
            print("获取价格模板信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return agreement_info_dict

    # 任务执行
    def task(self):
        q = Queue(maxsize=250)  # 设定最大队列数和线程数

        count = 1

        hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
        hongde_merchant_ids.extend(self.hongde_group)

        # 获取所有除宏德库存以外的记录
        stock_related_list = self.get_stock_related(hongde_merchant_ids)

        # stock_related_list = self.get_stock_related2("9ca10cc4dfd044e8ab6d34cd41f92428")

        print(stock_related_list)

        for stock_related in stock_related_list:

            # print(stock_related)

            # 暂时只跑F6D1CF5AB975B20D7F30F55BE66272B6
            # if stock_related.get("erp_uid") != "de0463bbfcbe0d40ef0c1242d3281ab5":
            #     print(stock_related.get("merchant_name"), stock_related.get("erp_uid"))
            #     continue

            # 车福林
            if stock_related.get("erp_uid") != "F6D1CF5AB975B20D7F30F55BE66272B6":
                continue

            # if stock_related.get("erp_uid") != "bc10e8b1f8a3921ccc2ecd0cd36ef27":
            #     continue

            print(stock_related)

            goods_cst_id = stock_related.get("goods_cst_id")

            # 获取库存商品列表
            goods_list = self.get_cst_goods(goods_cst_id)

            # print(len(goods_list))

            check_count = 0

            for goods in goods_list:

                # 查询是否存在多个价格，不存在则直接break
                multiple_price = False
                sale_price_dict = {}
                err, res = self.check_multiple_price_exist(goods, stock_related)
                # print(res)
                if err:
                    print("获取失败")
                    continue
                else:
                    data = res.get('data')
                    if len(data['stocks']) == 0:
                        print(data['stocks'])
                        print(goods.get("code"))
                        print(goods.get("standard_code"))
                        print("无数据")
                        continue

                    for item in data['stocks']:
                        goods_code = item.get("goods_code")
                        if goods.get("code") != goods_code:
                            continue
                        else:
                            # 看是否存在sale_price2、3、4、5、6
                            for key, value in item.items():
                                if key == "sale_price":
                                    sale_price_dict[key] = value
                                    multiple_price = True

                            if multiple_price:
                                break

                # 最后如果确定没有多个价格，则break
                if not multiple_price:
                    # if check_count == 10:
                    #     break
                    # else:
                    #     check_count += 1
                    #     print("没有多个价格")
                    #     continue

                    print("没有多个价格")
                    continue
                else:
                    # check_count = 0

                    # print(sale_price_dict)
                    # 新建价格模板
                    # self.create_agreement_excel_info(stock_related, sale_price_dict)

                    # 获取价格模板字典
                    # agreement_info_dict = self.get_agreement_excel_dict(stock_related.get("merchant_id"))

                    t = threading.Thread(target=self.write_data, args=(goods, stock_related, sale_price_dict, 497))
                    q.put(t)
                    if q.full() == True:
                        thread_list = []
                        while q.empty() == False:
                            t = q.get()
                            thread_list.append(t)
                            t.start()
                        for t in thread_list:
                            t.join()

                    print("当前写入数：", count,
                          ", 商家为：", stock_related.get("merchant_name"),
                          ", 商品编码为：", goods.get("code"),
                          ", 名称为：", goods.get("part_name"))

                    count += 1

            # 处理残余队列数据
            thread_list = []
            while q.empty() == False:
                t = q.get()
                thread_list.append(t)
                t.start()
            for t in thread_list:
                t.join()


if __name__ == "__main__":
    InitAgreementPrice()